package com.budwk.app.util.poiUtils;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.text.StrFormatter;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.common.component.DictComponent;
import com.budwk.app.sqfw.constants.CommonConstants;
import com.budwk.app.sqfw.constants.DictConstants;
import com.budwk.app.sqfw.dto.HouseSurveyFormExcelParamDTO;
import com.budwk.app.sqfw.dto.HouseSurveyWordDTO;
import com.budwk.app.sqfw.enums.DownLoadFileNameEnum;
import com.budwk.app.sqfw.enums.HouseSurveyEnum;
import com.budwk.app.sqfw.models.Sqfw_cenSusPaper;
import com.budwk.app.sqfw.models.Sqfw_formSummary;
import com.budwk.app.sqfw.vo.GenerateWordAndPdfUrlVO;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.sys.vo.SysDictVO;
import com.budwk.app.util.StrUtil;
import com.budwk.app.util.poiUtils.excelPictureConfigBO.ExcelFileCreateParamBO;
import com.budwk.app.util.poiUtils.excelPictureConfigBO.HouseSurveyFormExcelSNSQ;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.security.utils.SecurityUtil;
import com.google.common.base.Joiner;
import com.google.common.base.Splitter;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.commons.lang3.StringUtils;

import java.io.ByteArrayOutputStream;
import java.util.*;
import java.util.stream.Collectors;

import static com.budwk.app.util.poiUtils.CommonFormOfficeUtils.*;

/**
 * @author ZY.Wang
 * @since 2023/9/4
 */
public class HouseSurveyFormOfficeUtils {

    private static final String MOVEOUT = "搬离";

    private static final String DEAD = "死亡";

    /**
     * 生成入户调查表Pdf
     *
     * @param formSummary word信息
     * @return 结果
     */
    public static GenerateWordAndPdfUrlVO createWordAndPdfUrlCheckForm(HouseSurveyWordDTO formSummary) {
        long startTime = System.currentTimeMillis();

        List<ByteArrayOutputStream> osList = Lists.newArrayList();

        String a16 = formSummary.getA16();
        String[] a16s = a16.split(",");
        for (String a16Type : a16s) {

            if (a16Type.equals(HouseSurveyEnum.WINTER_PARTROL.getName())) {
                // 生成冬巡pdf
                ByteArrayOutputStream docOS = createWinterPatrol(formSummary);
                osList.add(docOS);
                continue;
            }

            if (a16Type.equals(HouseSurveyEnum.NINE_TYPES_OF_PERSONNEL_PATROL.getName())) {
                // 九类人员巡查df
                ByteArrayOutputStream docOS = nineTypesOfPersonnelPatrolPdf(formSummary);
                osList.add(docOS);
                continue;
            }

            Map<String, Object> paramMap = Maps.newHashMap();

            String unitName = formSummary.getCompleteUnitName();

            paramMap.put("unitName", unitName);
            paramMap.put("a16Type", a16Type);

            String fillDateFormatStr = "yyyy 年 MM 月 dd 日";
            Date fillDate = formSummary.getFillDate();
            if (fillDate == null) fillDate = formSummary.getCreateDate();
            String fillDateStr = DateUtil.format(fillDate, fillDateFormatStr);
            paramMap.put("fillDateStr", fillDateStr);

            paramMap.put("a1", formSummary.getA1());
            paramMap.put("a4", formSummary.getA4());
            paramMap.put("a5", formSummary.getA5());
            paramMap.put("a2", formSummary.getA2());
            paramMap.put("a8", formSummary.getA8());

            if (StringUtils.isNotEmpty(formSummary.getA11())) {
                paramMap.put("a11s", addWordPicture(formSummary.getA11(), "a11", 8F));
            } else {
                paramMap.put("a11s", null);
            }
            if (StringUtils.isNotEmpty(formSummary.getA12())) {
                paramMap.put("a12s", addWordPicture(formSummary.getA12(), "a12", 8F));
            } else {
                paramMap.put("a12s", null);
            }

            String a20 = StrUtil.formatStr(formSummary.getA20());
            if ("入户成功".equals(a20) || a20.equals("无")) a20 = StringUtils.EMPTY;
            paramMap.put("a20", a20);
            ByteArrayOutputStream docOS = CommonFormOfficeUtils.generateWordDocument(paramMap, "houseSurveyFormSNSQ.docx", StrFormatter.format("{}入户调查表", a16Type), startTime);
            osList.add(docOS);
        }
        //String fileName = "进家入户宣传登记表集合";
        //zhangp 2023-12-13 文件名格式统一
        String fileName = DownLoadFileNameEnum.HOME_REGISTRATION_PDF.getName();
        return wordToPdfAndUpload(osList, fileName, fileName);
    }

    /**
     * 生成冬巡pdf
     *
     * @param formSummary 提交表格内容
     * @return 结果
     */
    private static ByteArrayOutputStream createWinterPatrol(HouseSurveyWordDTO formSummary) {

        long startTime = System.currentTimeMillis();

        Map<String, Object> paramMap = Maps.newHashMap();
        paramMap.put("county", formSummary.getCounty());
        paramMap.put("unitName", formSummary.getUnitName());
        paramMap.put("fillDateStr", DateUtil.format(formSummary.getFillDate(), "yyyy-MM-dd HH:mm:ss"));
        paramMap.put("a2", formSummary.getA2());

        paramMap.put("addressDetail", StrUtil.getHouseAddress(formSummary));
        paramMap.put("a8", formSummary.getA8());
        paramMap.put("a17", formSummary.getA17());
        // 商住楼/公寓和公寓统称为公寓
        String a18 = StrUtil.formatStr(formSummary.getA18());
        if (StringUtils.isNotEmpty(a18)) {
            if ("商住楼/公寓".equals(a18) || "公寓".equals(a18)) {
                a18 = "公寓";
            }
        }
        paramMap.put("a18", a18);
        boolean a9_1 = true, a9_2 = false, a9_3 = false, a9_4 = false, a9_5 = false;
        String a9_6 = "";
        String a9 = formSummary.getA9();
        if (StringUtils.isNotBlank(a9) && !a9.equals("无")) {
            a9_1 = false;
            a9_2 = true;
            HashSet<String> a9Set = Sets.newHashSet(Splitter.on(",").split(a9));
            if (a9Set.contains("乱拉乱接")) a9_3 = true;
            if (a9Set.contains("使用假冒伪劣产品")) a9_4 = true;
            if (a9Set.contains("超负荷用电")) a9_5 = true;

            a9_6 = getPremisesStr(a9Set, null);
        }
        paramMap.put("a9_1", a9_1);
        paramMap.put("a9_2", a9_2);
        paramMap.put("a9_3", a9_3);
        paramMap.put("a9_4", a9_4);
        paramMap.put("a9_5", a9_5);
        paramMap.put("a9_6", a9_6);

        boolean a10_1 = false, a10_2 = false, a10_3 = false, a10_4 = false, a10_5 = true;
        String a10_6 = "";
        String a10 = formSummary.getA10();
        if (StringUtils.isNotBlank(a10) && !a10.equals("无")) {
            a10_1 = true;
            a10_5 = false;
            Set<String> a10Set = Sets.newHashSet(Splitter.on(",").split(a10));
            if (a10Set.contains("室内充电")) a10_2 = true;
            if (a10Set.contains("室外充电")) a10_3 = true;
            if (a10Set.contains("飞线充电")) a10_4 = true;
            a10_6 = getPremisesStr(a10Set, null);
        }
        paramMap.put("a10_1", a10_1);
        paramMap.put("a10_2", a10_2);
        paramMap.put("a10_3", a10_3);
        paramMap.put("a10_4", a10_4);
        paramMap.put("a10_5", a10_5);
        paramMap.put("a10_6", a10_6);

        boolean a6_1 = false, a6_2 = true;
        String a6_3 = "";
        String a6 = formSummary.getA6();
        if (StringUtils.isNotBlank(a6) && !a6.equals("否")) {
            a6_1 = true;
            a6_2 = false;
            Set<String> a6Set = Sets.newHashSet(Splitter.on(",").split(a6));
            a6_3 = getPremisesStr(a6Set, null);
        }
        paramMap.put("a6_1", a6_1);
        paramMap.put("a6_2", a6_2);
        paramMap.put("a6_3", a6_3);

        boolean a13_1 = false, a13_3 = true;
        String a13_2 = "", a13_4 = "";
        String a13 = formSummary.getA13();
        if (StringUtils.isNotBlank(a13) && !a13.equals("否")) {
            a13_1 = true;
            a13_3 = false;
            Set<String> a13Set = Sets.newHashSet(Splitter.on(",").split(a13));
            a13_2 = getPremisesStr(a13Set, "情况:");
            a13_4 = getPremisesStr(a13Set, null);
        }
        paramMap.put("a13_1", a13_1);
        paramMap.put("a13_2", a13_2);
        paramMap.put("a13_3", a13_3);
        paramMap.put("a13_4", a13_4);

        List<String> noSigCreateUserList = Lists.newArrayList();
        List<String> sigCreateUserList = Lists.newArrayList();
        if (StringUtils.isBlank(formSummary.getCreateUserSigUrl()))
            noSigCreateUserList.add(formSummary.getCreateUserName());
        else sigCreateUserList.add(formSummary.getCreateUserSigUrl());
        addWordUserSignature(paramMap, "cuNoSig", "cuSigs", "cuSig", noSigCreateUserList, sigCreateUserList, 1.0F);

        paramMap.put("a14", formSummary.getA14().replaceFirst("有,", "").replace("其他:", ""));
        // 宣传教育培调
        paramMap.put("a21", StringUtils.isNotEmpty(formSummary.getA21()) && formSummary.getA21().equals("是"));
        paramMap.put("a22", StringUtils.isNotEmpty(formSummary.getA22()) && formSummary.getA22().equals("是"));
        return CommonFormOfficeUtils.generateWordDocument(paramMap, "hourseSurveyEntryHouseRecord.docx", "冬巡巡查表", startTime);
    }

    /**
     * 生成九类人员巡查表
     *
     * @param formSummary
     * @return
     */
    private static ByteArrayOutputStream nineTypesOfPersonnelPatrolPdf(HouseSurveyWordDTO formSummary) {
        long startTime = System.currentTimeMillis();

        Map<String, Object> paramMap = Maps.newHashMap();
        paramMap.put("community", formSummary.getCounty());
        paramMap.put("fillDate", DateUtil.format(formSummary.getFillDate(), "yyyy-MM-dd HH:mm:ss"));
        paramMap.put("createBy", formSummary.getCreateUserName());

        paramMap.put("address", StrUtil.getHouseAddress(formSummary));

        paramMap.put("name", null);
        paramMap.put("specialMark", null);
        paramMap.put("phone", null);
        paramMap.put("condition", null);
        if (formSummary.getParamData() != null) {
            List<Sqfw_cenSusPaper> paramDataList = JSONUtil.toList(JSONUtil.parseArray(formSummary.getParamData()), Sqfw_cenSusPaper.class);
            // 只显示第一个住户信息
            paramMap.put("name", paramDataList.get(0).getName());
            paramMap.put("specialMark", paramDataList.get(0).getSpecialMark());
            paramMap.put("phone", paramDataList.get(0).getPhone());
            String condition = "家中无人，需二次探访";
            if (!"无人".equals(paramDataList.get(0).getSurvey())) {
                String str = StringUtils.isNotBlank(paramDataList.get(0).getUserNotes()) ? paramDataList.get(0).getUserNotes() : "";
                condition = "叮嘱住户注意用火、用电、用气安全，发放消防安全知识宣传资料；暂未发现安全隐患；" + str;
            }
            paramMap.put("condition", condition);
        }

        if ("空置".equals(formSummary.getA17())) paramMap.put("condition", "该房屋已空置");

        //设置图片
        setA11Picture(formSummary, paramMap);
        setA12Picture(formSummary, paramMap);

        return CommonFormOfficeUtils.generateWordDocument(paramMap, "nineTypesOfPersonnelPatrol.docx", "九类人员巡查表", startTime);
    }

    /**
     * 九类人员巡查台账9
     *
     * @param paramDataList
     * @param isCheck
     * @return
     */
    public static String nineTypesOfPersonnelPatrolExcel(List<Sqfw_cenSusPaper> paramDataList, String... isCheck) {
        String isCheckData = isCheck.length > 0 ? isCheck[0] : null;
        Map<String, Object> excelMap = Maps.newHashMap();
        SysUserService sysUserService = (SysUserService) NutzIocUtils.getBeanByType(SysUserService.class);
        Sys_user user = sysUserService.fetch(SecurityUtil.getUserId());
        excelMap.put("county", user.getCounty());
        excelMap.put("unitName", user.getUnitName());
        excelMap.put("creatDate", DateUtil.format(new Date(), "yyyy年MM月dd日"));

        List<Map<String, Object>> dataList = Lists.newArrayList();
        int num = 0;
        for (Sqfw_cenSusPaper param : paramDataList) {
            //zhangp 2023-12-19 检查只要有一条数据就直接返回
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                return "true";
            }
            num++;
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("num", num);
            dataMap.put("specialMark", param.getSpecialMark());
            dataMap.put("name", param.getName());
            /*
            String condition = "家中无人，需二次探访";
            if (!"无人".equals(paramDataList.get(0).getSurvey())) {
                condition = "叮嘱住户注意用火、用电、用气安全，发放消防安全知识宣传资料；暂未发现安全隐患";
            }
            dataMap.put("condition", condition);

            dataMap.put("problem", "暂未发现异常情况");
            */
            //zhangp 20240-01-16 自动获取信息
            dataMap.put("condition", param.getPatrolSum());

            dataMap.put("problem", param.getIdentifyIssue());

            dataMap.put("fillDate", param.getFillDateStr());

            String phone = namePhone(param.getName(), param.getPhone());
            dataMap.put("phone", phone);
            String emergencyPhone = namePhone("紧急联系人电话", param.getEmergencyPhone());
            dataMap.put("emergencyPhone", StringUtils.isBlank(phone + emergencyPhone) ? "暂无" : emergencyPhone);
            /*
            String createUserPhone = namePhone(param.getCreateUserName(), param.getCreateUserPhone());
            dataMap.put("createBy_createUserPhone", StringUtils.isBlank(createUserPhone) ? "暂无" : createUserPhone);
            */
            //zhangp 2024-01-15 改为提交人姓名跟手机号
            String createUserPhone = StringUtils.isNotBlank(param.getUpadteUserName()) ? namePhone(param.getUpadteUserName(), param.getUpadteUserPhone()) : namePhone(param.getCreateUserName(), param.getCreateUserPhone());
            dataMap.put("createBy_createUserPhone", StringUtils.isBlank(createUserPhone) ? "暂无" : createUserPhone);

            dataMap.put("address", param.getCounty() + param.getCommunity() + StrUtil.getHouseAddress(param));
            dataMap.put("userNotes", param.getUserNotes());

            String a11 = param.getA11();
            String a12 = param.getA12();
            String url = null;
            if (StringUtils.isNotBlank(a11)) {
                url = a11;
            }
            //zhangp 2024-01-23 只取巡查图片
            /*
            if (StringUtils.isNotBlank(a12)) {
                url = a12;
            }
            if (StringUtils.isNotBlank(a11) && StringUtils.isNotBlank(a12)) {
                url = a11 + "," + a12;
            }
            */
            //图片处理
            List<byte[]> imgBytes = PictureUtils.scaleImageToByte(url, 6, true);
            dataMap.put("img", imgBytes);
            dataList.add(dataMap);

        }

        //zhangp 2023-12-04 检查是否能生成数据时使用
        if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
            if (dataList.isEmpty()) {
                return "false";
            }
        }

        String path = getTemplatePathByOS("nineTypesOfPersonnelPatrol.xlsx");
        //String fileName = "【九类人员巡查】台账" + DateUtil.format(new Date(), "yyyyMMddHHmmss");
        String fileName = String.format("%s%s", DownLoadFileNameEnum.NINE_TYPES_OF_PERSONNEL_PATROL.getName(), DateUtil.format(new Date(), "yyyyMMddHHmmss"));
        ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
        excelFileCreateParam.setTemplatePath(path);
        excelFileCreateParam.setSheetParam(excelMap);
        excelFileCreateParam.setDataList(dataList);
        excelFileCreateParam.setClazz(HouseSurveyFormExcelSNSQ.class);
        excelFileCreateParam.setFileName(fileName);
        excelFileCreateParam.setReturnWebUrlFlag(true);
        excelFileCreateParam.setRowHeight(180);
        excelFileCreateParam.setImgWidth(480);
        return createExcelAndUpload(excelFileCreateParam);
    }

    /**
     * 附件4：XX居委会“老、弱、病、残、酒、疯”六类人员台账
     *
     * @param paramDataList
     * @param isCheck
     * @return
     */
    public static String sixCategoriesOfPersonnel(List<Sqfw_cenSusPaper> paramDataList, String... isCheck) {
        String isCheckData = isCheck.length > 0 ? isCheck[0] : null;
        Map<String, Object> excelMap = Maps.newHashMap();
        SysUserService sysUserService = (SysUserService) NutzIocUtils.getBeanByType(SysUserService.class);
        Sys_user user = sysUserService.fetch(SecurityUtil.getUserId());
        excelMap.put("county", user.getCounty());
        excelMap.put("unitName", user.getUnitName());
        excelMap.put("creatDate", DateUtil.format(new Date(), "yyyy年MM月dd日"));

        List<Map<String, Object>> dataList = Lists.newArrayList();
        int num = 0;
        for (Sqfw_cenSusPaper param : paramDataList) {
            //zhangp 2023-12-19 检查只要有一条数据就直接返回
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                return "true";
            }
            num++;
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("num", num);
            dataMap.put("community", param.getCommunity());
            dataMap.put("specialMark", param.getSpecialMark());
            dataMap.put("name", param.getName());
            dataMap.put("age", param.getAge());
            dataMap.put("phone", param.getPhone());
            dataMap.put("address", param.getCounty() + param.getCommunity() + StrUtil.getHouseAddress(param));
            /*
            dataMap.put("createUserName", param.getCreateUserName());
            dataMap.put("createUserPhone", param.getCreateUserPhone());
            */
            //zhangp 2024-01-15 改为提交人姓名跟手机号
            dataMap.put("createUserName", StringUtils.isNotBlank(param.getUpadteUserName()) ? param.getUpadteUserName() : param.getCreateUserName());
            dataMap.put("createUserPhone", StringUtils.isNotBlank(param.getUpadteUserPhone()) ? param.getUpadteUserPhone() : param.getCreateUserPhone());

            String emergencyPhone = namePhone("紧急联系人电话", param.getEmergencyPhone());
            dataMap.put("emergencyPhone", StringUtils.isBlank(emergencyPhone) ? "暂无" : emergencyPhone);

            String a11 = param.getA11();
            String a12 = param.getA12();
            String url = null;

            if (StringUtils.isNotBlank(a11)) {
                url = a11;
            }
            //zhangp 2024-01-23 只取巡查图片
           /*
            if (StringUtils.isNotBlank(a12)) {
                url = a12;
            }
            if (StringUtils.isNotBlank(a11) && StringUtils.isNotBlank(a12)) {
                url = a11 + "," + a12;
            }
            */
            //图片处理
            List<byte[]> imgBytes = PictureUtils.scaleImageToByte(url, 4, true);
            dataMap.put("img", imgBytes);
            dataList.add(dataMap);

        }

        //zhangp 2023-12-04 检查是否能生成数据时使用
        if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
            if (dataList.isEmpty()) {
                return "false";
            }
        }

        String path = getTemplatePathByOS("sixCategoriesOfPersonnel.xlsx");
        //String fileName = "【六类人员】台账" + DateUtil.format(new Date(), "yyyyMMddHHmmss");
        String fileName = String.format("%s%s", DownLoadFileNameEnum.SIX_CATEGORIES_OF_PERSONNEL.getName(), DateUtil.format(new Date(), "yyyyMMddHHmmss"));

        ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
        excelFileCreateParam.setTemplatePath(path);
        excelFileCreateParam.setSheetParam(excelMap);
        excelFileCreateParam.setDataList(dataList);
        excelFileCreateParam.setClazz(HouseSurveyFormExcelSNSQ.class);
        excelFileCreateParam.setFileName(fileName);
        excelFileCreateParam.setReturnWebUrlFlag(true);
        return createExcelAndUpload(excelFileCreateParam);
    }

    private static String namePhone(String name, String phone) {
        StringBuilder stringBuilder = new StringBuilder();
        if (StringUtils.isNotBlank(phone)) stringBuilder.append(name).append("(").append(phone).append(")");
        return stringBuilder.toString();
    }

    private static boolean setA11Picture(Sqfw_formSummary formSummary, Map<String, Object> dataMap) {
        List<Map> pictures = new ArrayList<>();
        String a11 = formSummary.getA11();
        if (StringUtils.isNotBlank(a11)) {
            String[] door_picture = a11.split(",");
            for (String url : door_picture) {
                if (!cn.hutool.core.util.StrUtil.isBlank(url)) {
                    Map<String, Object> pictureMap = Maps.newHashMap();
                    pictureMap.put("a11", PictureUtils.scaleImage(url, 200, false));
                    pictures.add(pictureMap);
                }
            }
        }
        dataMap.put("a11s", pictures);
        return pictures.size() == 0 ? false : true;
    }

    private static boolean setA12Picture(Sqfw_formSummary formSummary, Map<String, Object> dataMap) {
        List<Map> pictures = new ArrayList<>();
        String a12 = formSummary.getA12();
        if (StringUtils.isNotBlank(a12)) {
            String[] door_picture = a12.split(",");
            for (String url : door_picture) {
                if (!cn.hutool.core.util.StrUtil.isBlank(url)) {
                    Map<String, Object> pictureMap = Maps.newHashMap();
                    pictureMap.put("a12", PictureUtils.scaleImage(url, 200, false));
                    pictures.add(pictureMap);
                }
            }
        }
        dataMap.put("a12s", pictures);
        return pictures.size() == 0 ? false : true;
    }


    public static String createHouseSurveyExcel(HouseSurveyFormExcelParamDTO excelParamDTO, String isCheckData) {
        Map<String, Object> excelParam = new HashMap<>();
        excelParam.put("unitName", excelParamDTO.getCompleteUnitName());
        excelParam.put("a16Type", excelParamDTO.getA16Type());
        List<Map<String, Object>> listMap = new ArrayList<>();
        List<Sqfw_formSummary> formSummaryList = excelParamDTO.getFormSummaryList();
        for (int i = 0; i < formSummaryList.size(); i++) {
            Sqfw_formSummary formSummary = formSummaryList.get(i);
            Map<String, Object> lm = new HashMap<>();
            lm.put("num", i + 1 + "");

            lm.put("a1", formSummary.getA1());

            lm.put("a4", formSummary.getA4());

            lm.put("a5", formSummary.getA5());

            lm.put("a2", formSummary.getA2());

            lm.put("a8", formSummary.getA8());

            String a11 = formSummary.getA11();
            List<byte[]> a11ByteList = Lists.newArrayList();
            //zhangp 2023-11-28 改 增加判断
            if (StringUtils.isNotBlank(a11)) {
                List<String> a11UrlStrList = Lists.newArrayList(a11.split(","));
                if (a11UrlStrList.size() > 1) a11UrlStrList = a11UrlStrList.subList(0, 1);
                a11ByteList = addPictureToByteList(a11UrlStrList);
            }

            lm.put("a11", a11ByteList);

            String a12 = formSummary.getA12();
            List<byte[]> a12ByteList = Lists.newArrayList();
            if (StringUtils.isNotBlank(a12)) {
                List<String> a12UrlStrList = Lists.newArrayList(a12.split(","));
                if (a12UrlStrList.size() > 1) a12UrlStrList = a12UrlStrList.subList(0, 1);
                a12ByteList = addPictureToByteList(a12UrlStrList);
            }
            lm.put("a12", a12ByteList);

            String a20 = formSummary.getA20();
            if (a20.equals("入户成功") || a20.equals("无")) a20 = "";
            lm.put("a20", a20);

            Date fillDate = formSummary.getFillDate();
            if (fillDate == null) fillDate = formSummary.getCreateDate();
            lm.put("fillDateStr", DateUtil.format(fillDate, "yyyy.MM.dd"));

            listMap.add(lm);
            //zhangp 2023-12-07 检查是否能生成数据时使用,只要有一条数据就返回
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                return "true";
            }
        }
        //zhangp 2023-12-04 检查是否能生成数据时使用
        if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
            if (listMap.isEmpty()) {
                return "false";
            }
        }
        excelParam.put("list", listMap);

        String path = getTemplatePathByOS("houseSurveyFormSNSQ.xlsx");

        //String fileName = StrFormatter.format("Excel{}入户调查", excelParamDTO.getA16Type());
        String fileName = DownLoadFileNameEnum.getNameVagueByName(excelParamDTO.getA16Type());
        if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
            fileName = fileName + excelParamDTO.getFileNameSuffix();

        ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
        excelFileCreateParam.setTemplatePath(path);
        excelFileCreateParam.setSheetParam(excelParam);
        excelFileCreateParam.setDataList(listMap);
        excelFileCreateParam.setClazz(HouseSurveyFormExcelSNSQ.class);
        excelFileCreateParam.setFileName(fileName);
        excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

        return createExcelAndUpload(excelFileCreateParam);
    }

    public static Map<String, Object> getHouseSurveySixContent(Sqfw_formSummary summary, Sqfw_cenSusPaper cenPeople, HouseSurveyFormExcelParamDTO param, List<Map<String, Object>> dataList) {
        Map<String, Object> dataMap = Maps.newHashMap();
        dataMap.put("num", dataList.size() + 1);
        dataMap.put("community", cenPeople.getCommunity());
        dataMap.put("grid", cenPeople.getGrid());
        dataMap.put("name", cenPeople.getName());
        dataMap.put("cardId", cenPeople.getCardId());

        dataMap.put("detailAddress", cenPeople.getDetailAddress());
        dataMap.put("specialMark", cenPeople.getSpecialMark());
        dataMap.put("phone", cenPeople.getPhone());

        String familyMemberPhone = "无";
        List<Sqfw_cenSusPaper> familyMemberList = param.getFamilyMemberInfoMap().get(cenPeople.getId());
        if (familyMemberList.size() > 0) {
            List<String> phoneList = Lists.newArrayList();
            for (Sqfw_cenSusPaper memberPaper : familyMemberList) {
                if (StringUtils.isNotBlank(memberPaper.getPhone())) {
                    phoneList.add(memberPaper.getPhone());
                }
            }
            String phoneStr = Joiner.on("、").join(phoneList);
            if (StringUtils.isNotBlank(phoneStr)) familyMemberPhone = phoneStr;
        }
        dataMap.put("familyMemberPhone", familyMemberPhone);
        dataMap.put("createUsername", summary.getCreateUserName());

        // 共同生活的人
        String peopleCountInfo = "无";
        if (familyMemberList.size() > 0) peopleCountInfo = "亲属";
        dataMap.put("peopleCountInfo", peopleCountInfo);

        dataMap.put("a6", StrUtil.formatStr(summary.getA6()));

        String houseSafetyInfo = "无";
        List<String> houseSafetyInfoList = Lists.newArrayList();

        String a9 = summary.getA9();
        String a10 = summary.getA10();
        if (StringUtils.isNotBlank(a9) && !a9.equals("无")) houseSafetyInfoList.add(a9);
        if (StringUtils.isNotBlank(a10) && !a9.equals("无")) houseSafetyInfoList.add(a10);
        String a14 = StrUtil.formatStr(summary.getA14());
        if (StringUtils.isNotEmpty(a14) && a14.contains(CommonConstants.HAVE)) {
            a14 = houseSafetyInfoList.size() == 0 ? a14.replace("有,其他:", "") : a14.replace("有,其他", "");
            houseSafetyInfoList.add(a14);
        }
        if (houseSafetyInfoList.size() > 0) houseSafetyInfo = Joiner.on("，").join(houseSafetyInfoList);
        dataMap.put("houseSafetyInfo", houseSafetyInfo);

        dataMap.put("a16", summary.getA16());

        List<byte[]> a11Bytes = addPictureToByteList(summary.getA11(), 1);
        dataMap.put("a11", a11Bytes);

        dataMap.put("remark", StrUtil.formatStr(cenPeople.getRemark()));

        dataMap.put("createUserPhone", StrUtil.formatStr(cenPeople.getCreateUserPhone()));
        dataMap.put("fillDate", "-");
        if (summary.getFillDate() != null) {
            String fillDateStr = DateUtil.format(summary.getFillDate(), DatePattern.NORM_DATETIME_PATTERN);
            dataMap.put("fillDate", fillDateStr);
        }
        return dataMap;
    }

    public static String createHouseSurveySixTypePeopleStandingBookExcel(HouseSurveyFormExcelParamDTO param, String isCheckData) {
        SysUserService sysUserService = (SysUserService) NutzIocUtils.getBeanByType(SysUserService.class);
        Sys_user user = sysUserService.fetch(SecurityUtil.getUserId());
        Map<String, Object> excelParam = new HashMap<>();
        excelParam.put("unitName", param.getCompleteUnitName());
        excelParam.put("county", user.getCounty());
        excelParam.put("createDateStr", DateUtil.format(new Date(), DatePattern.PURE_DATE_PATTERN));

        // 用于去掉重复数据 现在的规则是 unitId a1 a4 a5 身份证号 名字 特殊类别相同的 为一条数据
        List<Map<String, Object>> dataList = Lists.newArrayList();
        // 从数据字典获取特殊类型数据
        List<SysDictVO> dicts = DictComponent.getSpecialPersonnelTypes();
        dicts.removeIf(e -> e.getCode().equals(DictConstants.SPECIAL_PERSONNEL_TYPES_NO));
        List<Sqfw_formSummary> formSummaryList = param.getFormSummaryList();
        for (Sqfw_formSummary formSummary : formSummaryList) {
            String a7 = formSummary.getA7();
            if (StringUtils.isEmpty(a7)) {
                continue;
            }
            // 不是正常的均可以进入该台账
            List<Sqfw_cenSusPaper> cenPeopleList = JSONObject.parseArray(a7, Sqfw_cenSusPaper.class);
            for (Sqfw_cenSusPaper cenPeople : cenPeopleList) {
                if (StringUtils.isBlank(cenPeople.getId())) continue;
                String specialMark = cenPeople.getSpecialMark();
                if (StringUtils.isEmpty(specialMark)) continue;
                Optional<SysDictVO> optional = dicts.stream().filter(e -> specialMark.contains(e.getName())).findFirst();
                if (optional.isEmpty()) {
                    continue;
                }
                // 获取内容体
                Map<String, Object> dataMap = getHouseSurveySixContent(formSummary, cenPeople, param, dataList);
                dataList.add(dataMap);
            }
            //zhangp 2023-12-07 检查是否能生成数据时使用,只要有一条数据就返回
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (!dataList.isEmpty()) {
                    return "true";
                }
            }
        }
        //zhangp 2023-12-04 检查是否能生成数据时使用
        if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
            if (dataList.isEmpty()) {
                return "false";
            }
        }
        excelParam.put("list", dataList);
        String path = getTemplatePathByOS("houseSurveySixPeopleStandingBook.xlsx");
        //String fileName = String.format("%s%s", "Excel", HouseSurveyEnum.SIX_TYPES_PERSON_LEDGER.getName());
        String fileName = DownLoadFileNameEnum.SIX_TYPES_PERSON_LEDGER.getName();
        if (StringUtils.isNotBlank(param.getFileNameSuffix())) {
            fileName = fileName + param.getFileNameSuffix();
        }

        ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
        excelFileCreateParam.setTemplatePath(path);
        excelFileCreateParam.setSheetParam(excelParam);
        excelFileCreateParam.setDataList(dataList);
        excelFileCreateParam.setClazz(HouseSurveyFormExcelSNSQ.class);
        excelFileCreateParam.setFileName(fileName);
        excelFileCreateParam.setReturnWebUrlFlag(param.getReturnWebUrlFlag());

        return createExcelAndUpload(excelFileCreateParam);
    }


    /**
     * 导出进家入户宣传台账数据
     *
     * @param param 参数
     * @return 导出成功后的URL
     */
    public static String exportHomeToHomePromotionLedger(HouseSurveyFormExcelParamDTO param, String isCheckData) {
        Map<String, Object> excelParam = new HashMap<>();
        // 组装数据
        SysUserService sysUserService = (SysUserService) NutzIocUtils.getBeanByType(SysUserService.class);
        Sys_user user = sysUserService.fetch(SecurityUtil.getUserId());
        excelParam.put("county", user.getCounty());
        //excelParam.put("createBy", SecurityUtil.getUserUsername());
        //zhangp 2024-01-08 直接从查询出来的数据中取值
        excelParam.put("createBy", user.getUsername());
        excelParam.put("crateDate", DateUtil.format(new Date(), DatePattern.PURE_DATE_PATTERN));
        List<Map<String, Object>> dataList = Lists.newArrayList();
        List<Sqfw_formSummary> formSummaryList = param.getFormSummaryList();
        //zhangp 2023-11-28 改 模板下载放行
       /* if (formSummaryList == null || formSummaryList.size() == 0) {
            throw new AppException("入户数据不存在");
        }*/
        int startNumber = 1; // 起始序号
        // 取出冬巡的数据
        List<Sqfw_formSummary> dsList = formSummaryList.stream().
                filter(f -> StringUtils.isNotEmpty(f.getA16()) && f.getA16().contains(HouseSurveyEnum.WINTER_PARTROL.getName())).
                collect(Collectors.toList());
        //zhangp 2023-11-28 改 模板下载放行
        /*if (dsList.size() == 0) {
            throw new AppException("入户数据不存在");
        }*/
        for (Sqfw_formSummary formSummary : dsList) {
            Map<String, Object> dataMap = new HashMap<>();
            // 获取进家入户宣传台账内容体
            String[] content = getPromotionLedgerContent(startNumber, formSummary);
            for (int i = 0; i < content.length; i++) {
                dataMap.put(String.format("%s%s", "c", (i + 1)), content[i]);
            }
            // 图片
            dataMap.put("a11", addPictureToByteList(formSummary.getA11(), 1));
            dataMap.put("a12", addPictureToByteList(formSummary.getA12(), 1));
            dataList.add(dataMap);
            startNumber++;
            //zhangp 2023-12-07 检查是否能生成数据时使用,只要有一条数据就返回
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                return "true";
            }
        }
        //zhangp 2023-12-04 检查是否能生成数据时使用
        if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
            if (dataList.isEmpty()) {
                return "false";
            }
        }
        excelParam.put("list", dataList);
        // 获取文档路径
        String path = getTemplatePathByOS("hourseSurveyHomePromotionLedger.xlsx");
        //String fileName = String.format("%s%s", "Excel", HouseSurveyEnum.HOME_PROMOTION_LEDGER.getName());
        String fileName = DownLoadFileNameEnum.HOME_PROMOTION_LEDGER.getName();

        if (StringUtils.isNotBlank(param.getFileNameSuffix()))
            fileName = fileName + param.getFileNameSuffix();

        ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
        excelFileCreateParam.setTemplatePath(path);
        excelFileCreateParam.setSheetParam(excelParam);
        excelFileCreateParam.setDataList(dataList);
        excelFileCreateParam.setClazz(HouseSurveyFormExcelSNSQ.class);
        excelFileCreateParam.setFileName(fileName);
        excelFileCreateParam.setReturnWebUrlFlag(param.getReturnWebUrlFlag());

        return createExcelAndUpload(excelFileCreateParam);
    }

    private static String[] getPromotionLedgerContent(int number, Sqfw_formSummary formSummary) {
        // 第二列
        String c2 = String.format("%s%s%s", StrUtil.formatStr(formSummary.getA1()),
                StrUtil.formatStr(formSummary.getA4()),
                StrUtil.formatStr(formSummary.getA5()));
        // 第三列
        String c3 = StrUtil.formatStr(formSummary.getA2());
        // 第四列
        String c4 = StrUtil.formatStr(formSummary.getA8());
        // 第五列
        String a17 = formSummary.getA17();
        String c5 = StringUtils.isNotEmpty(a17) && a17.equals("群租房") ? a17 : StrUtil.formatStr(formSummary.getA18());
        // 第六列
        String a9 = formSummary.getA9();
        String c6 = a9;
        if (StringUtils.isEmpty(a9) || "无".equals(a9)) {
            c6 = "合格";
        }
        // 第七列
        String c7 = StrUtil.formatStr(formSummary.getA10());
        // 第八列  如果包含煤炉或燃气气瓶或城市燃气灶，则为是 否则为否
        String a6 = formSummary.getA6();
        String c8 = CommonConstants.NO_CHINA;
        String[] a6s = {"煤炉", "燃气气瓶", "城市燃气灶"};
        if (StringUtils.isNotEmpty(a6)) {
            Optional<String> optional = Arrays.stream(a6s).filter(a6::contains).findFirst();
            c8 = optional.isPresent() ? CommonConstants.YES_CHINA : CommonConstants.NO_CHINA;
        }
        String c9 = formSummary.getA13();
        c9 = StringUtils.isNotEmpty(c9) && c9.contains(CommonConstants.YES_CHINA) ? c9.replace(",情况", "") : c9;
        String c10 = StrUtil.formatStr(formSummary.getA21());
        String c11 = StrUtil.formatStr(formSummary.getA22());
        String c12 = StrUtil.formatStr(formSummary.getUnitName());
        String c13 = StrUtil.formatStr(formSummary.getCreateUserName());
        String c14 = DateUtil.format(formSummary.getFillDate(), DatePattern.PURE_DATE_PATTERN);
        return new String[]{String.valueOf(number), c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14};
    }

    // 获取特殊人员的家庭数据 根据a1 a3 a4 a5去重
    private static List<Sqfw_formSummary> getLatestData(List<Sqfw_formSummary> formSummaryList) {
        // 对操作数据进行复制
        List<Sqfw_formSummary> newFormSummaryList = new ArrayList<>();
        for (Sqfw_formSummary sqfwFormSummary : formSummaryList) {
            Sqfw_formSummary sqfwFormSummary1 = new Sqfw_formSummary();
            BeanUtil.copyProperties(sqfwFormSummary, sqfwFormSummary1);
            // 如果a1 a3 a4为空，赋值为-  // a1:院落名称  a3:网格  a4: 栋  a5:单元户号（必填）
            sqfwFormSummary1.setA1(StringUtils.isEmpty(sqfwFormSummary.getA1()) ? "-" : sqfwFormSummary.getA1());
            sqfwFormSummary1.setA3(StringUtils.isEmpty(sqfwFormSummary.getA3()) ? "-" : sqfwFormSummary.getA3());
            sqfwFormSummary1.setA4(StringUtils.isEmpty(sqfwFormSummary.getA4()) ? "-" : sqfwFormSummary.getA4());
            //zhangp 2024-01-10 当房屋类型为自建房时A5为非必填
            sqfwFormSummary1.setA5(StringUtils.isEmpty(sqfwFormSummary.getA5()) ? "-" : sqfwFormSummary.getA5());
            newFormSummaryList.add(sqfwFormSummary1);
        }
        List<Sqfw_formSummary> results = new ArrayList<>();
        ArrayList<Sqfw_formSummary> collect = newFormSummaryList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() ->
                new TreeSet<>(Comparator.comparing(o -> o.getA1() + ";" + o.getA3() + ";" + o.getA4() + ";" + o.getA5()))), ArrayList::new));
        for (Sqfw_formSummary formSummary : collect) {
            Optional<Sqfw_formSummary> optional = newFormSummaryList.stream()
                    .filter(e -> formSummary.getA1().equals(e.getA1()) && formSummary.getA3().equals(e.getA3()) &&
                            formSummary.getA4().equals(e.getA4()) && formSummary.getA5().equals(e.getA5()))
                    .max(Comparator.comparing(Sqfw_formSummary::getCreateDate));
            if (optional.isPresent()) {
                Sqfw_formSummary sqfwFormSummary = formSummaryList.stream().filter(f -> f.getId().equals(optional.get().getId())).findFirst().get();
                results.add(sqfwFormSummary);
            }
        }
        return results;
    }

    public static Map<String, Object> buildCenSusPaperDataMap(List<Sqfw_cenSusPaper> specialMarkVos, int num, HouseSurveyFormExcelParamDTO param) {
        // 组装map
        Map<String, Object> dataMap = Maps.newHashMap();
        String[] specialMarkNames = {"oldAloneRemark", "oldManyRemark", "weakRemark", "sickRemark", "incompleteRemark", "winRemark", "madRemark"};
        String[] SpecialMarkValus = {"单独居老人现sum人", "双独居老人现sum人", "”弱“现sum人", "行动不便病人现sum人", "残疾人现sum人", "酗酒现sum人", "精神障碍患者（疯）现sum人"};
        String[] remarks = {"oldAloneSpeRemark", "oldManySpeRemark", "weakSpeRemark", "sickSpeRemark", "incompleteSpeRemark", "winSpeRemark", "madSpeRemark"};
        // 1 解析弱病残酒疯数据
        int i = 0;
        for (Map.Entry<String, String> entry : DictComponent.specialMarks().entrySet()) {
            // 查询当前特殊人员的所有数据
            List<Sqfw_cenSusPaper> sqfwCenSusPapers = specialMarkVos.stream().filter(e -> e.getSpecialMark().contains(entry.getValue())).collect(Collectors.toList());
            // 获取搬离或者死亡人数
            long move = sqfwCenSusPapers.stream().filter(w -> MOVEOUT.equals(w.getLatestStatus())).count();
            long dead = sqfwCenSusPapers.stream().filter(w -> DEAD.equals(w.getLatestStatus())).count();

            // 现有人员
            long nowPerson = sqfwCenSusPapers.size() - move - dead;
            // 存贮到map中
            dataMap.put(entry.getKey(), nowPerson);
            dataMap.put(specialMarkNames[i], SpecialMarkValus[i].replace("sum", String.valueOf(nowPerson)));
            // 获取备注信息
            String weakSpeRemark = getRemark(move, dead);
            dataMap.put(remarks[i], weakSpeRemark);
            i++;
        }


        // 搬离总人数
        long moveOutPerson = specialMarkVos.stream().filter(e -> StringUtils.isNotBlank(e.getLatestStatus()) && e.getLatestStatus().contains(MOVEOUT)).count();
        // 死亡总人数
        long deadPerson = specialMarkVos.stream().filter(e -> StringUtils.isNotBlank(e.getLatestStatus()) && e.getLatestStatus().contains(DEAD)).count();
        // 统计六类人员总人数（需要减去搬离和死亡的总人数）
        long total = sumSixPerson(specialMarkVos) - moveOutPerson - deadPerson;
        dataMap.put("num", num);
        dataMap.put("community", StrUtil.formatStr(param.getCompleteUnitName()));
        if (StringUtils.isEmpty(param.getCompleteUnitName())) {
            Sqfw_cenSusPaper cenSusPaper = specialMarkVos.get(0);
            dataMap.put("community", String.format("%s%s", StrUtil.formatStr(cenSusPaper.getCounty()), StrUtil.formatStr(cenSusPaper.getCommunity())));
        }
        dataMap.put("dead", deadPerson);
        dataMap.put("moveOut", moveOutPerson);
        dataMap.put("total", total);
        dataMap.put("remark", String.format("%s六类人员人数现%s人，其中", StrUtil.formatStr(param.getCounty()), total));
        return dataMap;
    }

    // 统计六类人员总人数（需要减去搬离和死亡的总人数）
    private static long sumSixPerson(List<Sqfw_cenSusPaper> specialMarkVos) {
        long total = 0;
        for (Sqfw_cenSusPaper specialMarkVo : specialMarkVos) {
            if (StringUtils.isEmpty(specialMarkVo.getSpecialMark())) {
                continue;
            }
            Collection<String> values = DictComponent.specialMarks().values();
            Optional<String> optional = values.stream().filter(e -> specialMarkVo.getSpecialMark().contains(e)).findAny();
            if (optional.isPresent()) {
                total++;
            }
        }
        return total;
    }


    public static String createHouseSurveySixTypePeopleStatistic(String fileNameSuffix, List<HouseSurveyFormExcelParamDTO> paramDTOList, boolean returnUrlFlag, String isCheckData) {
        Map<String, Object> excelMap = Maps.newHashMap();
        SysUserService sysUserService = (SysUserService) NutzIocUtils.getBeanByType(SysUserService.class);
        Sys_user user = sysUserService.fetch(SecurityUtil.getUserId());
        excelMap.put("county", user.getCounty());
        List<Map<String, Object>> dataList = Lists.newArrayList();
        boolean houseFlag = false;
        for (HouseSurveyFormExcelParamDTO param : paramDTOList) {
            int num = 1;
            List<Sqfw_cenSusPaper> specialMarkVos = new ArrayList();
            // 1 按照家庭户数，获取最新的家庭人员数据
            List<Sqfw_formSummary> formSummaryList = getLatestData(param.getFormSummaryList());
            for (Sqfw_formSummary formSummary : formSummaryList) {
                if (StringUtils.isEmpty(formSummary.getA7())) {
                    continue;
                }
                List<Sqfw_cenSusPaper> a7PaperList = JSONObject.parseArray(formSummary.getA7(), Sqfw_cenSusPaper.class);
                for (Sqfw_cenSusPaper a7Paper : a7PaperList) {
                    // 3 组装内容体
                    String houseAddress = String.format("%s%s%s%s%s", formSummary.getA1(), formSummary.getUnitId(), formSummary.getA4(), formSummary.getA5(), formSummary.getA3());
                    a7Paper.setHouseNumber(houseAddress);
                    specialMarkVos.add(a7Paper);
                }
            }
            // 4 组装数据
            Map<String, Object> dataMap = buildCenSusPaperDataMap(specialMarkVos, num, param);
            // 户数的统计 户数只计算老、弱、病、残、酒、疯这6类
            long houseCount = sumSurveySixTypePeopleHouse(specialMarkVos);
            if (houseCount > 0) {
                houseFlag = true;
                //zhangp 2023-12-07 检查是否能生成数据时使用,只要有一条数据就返回
                if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                    return String.valueOf(houseFlag);
                }
            }
            dataMap.put("houseCount", houseCount);
            dataList.add(dataMap);
            excelMap.put("list", dataList);
        }
        //zhangp 2023-12-04 检查是否能生成数据时使用
        if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
            return String.valueOf(houseFlag);
        }
        String path = getTemplatePathByOS("houseSurveySixPeopleStatistic.xlsx");
        //String fileName = "Excel六类重点人员分类数据统计表";
        String fileName = DownLoadFileNameEnum.SIX_KEYNOTE_TABLE_STATIS.getName();
        if (StringUtils.isNotBlank(fileNameSuffix))
            fileName = fileName + fileNameSuffix;

        ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
        excelFileCreateParam.setTemplatePath(path);
        excelFileCreateParam.setSheetParam(excelMap);
        excelFileCreateParam.setDataList(dataList);
        excelFileCreateParam.setClazz(HouseSurveyFormExcelSNSQ.class);
        excelFileCreateParam.setFileName(fileName);
        excelFileCreateParam.setReturnWebUrlFlag(returnUrlFlag);
        return createExcelAndUpload(excelFileCreateParam);
    }

    // 户数的统计 户数只计算老、弱、病、残、酒、疯这6类
    public static long sumSurveySixTypePeopleHouse(List<Sqfw_cenSusPaper> specialMarkVos) {
        // ●将表单移至【住户信息】页面【内容不变！！】，但判断原先countyard+housenumber判断条件需加入buildling；
        Set<String> set = new HashSet();
        for (Sqfw_cenSusPaper specialMarkVo : specialMarkVos) {
            String houseNumber = String.format("%s%s%s", StrUtil.formatStr(specialMarkVo.getCourtyard()),
                    StrUtil.formatStr(specialMarkVo.getHouseNumber()), StrUtil.formatStr(specialMarkVo.getBuilding()));
            set.add(houseNumber);
            specialMarkVo.setHouseNumber(houseNumber);
        }
        int sum = 0;
        for (String houseNumber : set) {
            List<Sqfw_cenSusPaper> collect1 = specialMarkVos.stream().filter(e -> houseNumber.equals(e.getHouseNumber())).collect(Collectors.toList());
            for (Sqfw_cenSusPaper cenSusPaper : collect1) {
                long count1 = DictComponent.specialMarks().values().stream().filter(e -> cenSusPaper.getSpecialMark().contains(e)).count();
                if (count1 > 0) {
                    sum++;
                    break;
                }
            }
        }
        return sum;
    }

    private static String getRemark(long moveOut, long dead) {
        List<String> oldAloneSpeList = Lists.newArrayList();
        if (moveOut > 0) {
            String OldAloneMoveOutRemark = moveOut + "人已搬走";
            oldAloneSpeList.add(OldAloneMoveOutRemark);
        }
        if (dead > 0) {
            String oldAloneDeadRemark = dead + "人已死亡";
            oldAloneSpeList.add(oldAloneDeadRemark);
        }
        if (oldAloneSpeList.size() > 0) {
            String oldAloneSpeStr = Joiner.on("，").join(oldAloneSpeList);
            if (StringUtils.isNotBlank(oldAloneSpeStr)) {
                return String.format("%s%s%s", "（原", oldAloneSpeStr, "）");
            }
        }
        return StringUtils.EMPTY;
    }

    private static String getPremisesStr(Collection<String> list, String keyword) {
        if (StringUtils.isBlank(keyword)) keyword = "其他:";

        String resultStr = "";
        for (String s : list) {
            if (s.contains(keyword)) {
                resultStr = s.replace(keyword, "");
                break;
            }
        }
        return resultStr;
    }
}
